#!/usr/bin/python
# -*- coding: UTF-8 -*-
import re
from itertools import islice

from mssql_tools import MSSQL


def format_num(n):
    if n<10:
        return "0"+str(n)
    return str(n)

#1906070829
def format_time(s):
    return "20"+s[0]+s[1]+"-"+s[2]+s[3]+"-"+s[4]+s[5]+" "+s[6]+s[7]+":"+s[8]+s[9]+":00"



rp = r'msgId=(.+),longMsgId=.+, stat=(.+), submitTime=(.+), doneTime=(.+), destterminalId=(86)?(.+), smscSequence=(.+)\]\]'
ms = MSSQL(host="192.168.4.241", user="sa", pwd="jy@88250860", db="EMAS_SMS")
ms.createConnect()

def gen_sql(fn,gwNum):
    row=0
    start_row=0;
    with open(fn, "r", encoding='utf-8') as f:
        count = 0
        while 1:
            # 一次读取1M
            size=1024*1024
            lines =f.readlines(size)
            if not lines:
                break
            # print(len(lines))
            for line in lines:
                row = row + 1
                #从第几行开始执行
                if(row<start_row):continue
                #去掉后面的回车和换行
                line=line.strip()
                searchObj = re.search(rp, line, re.M | re.I)

                if searchObj:
                    msgId = searchObj.group(1)
                    stat = searchObj.group(2)
                    submitTime = format_time(searchObj.group(3))
                    doneTime = format_time(searchObj.group(4))
                    mobile = searchObj.group(6)
                    seqId = searchObj.group(7)
                    # print(format_time(submitTime))

                    sql = "insert into sms_status(msgId, stat, submitTime, doneTime, mobile, seqId, gwNum) VALUES ('" + msgId + "', '" + stat + "', '" + submitTime + "', '" + doneTime + "', '" + mobile + "', '" + seqId + "'," + str(
                        gwNum) + ")"
                    # print(sql)
                    # wf.write(sql)
                    # wf.write(";\n")
                    count = count + 1
                    print("row:" + str(row) + " sql:" + sql)
                    ms.executeBatch(sql)  # 执行SQL，不提交
                    if (count >= 500):
                        count = 0
                        ms.commit()  # 每500个提交一次
                        print("commit")
                else:
                    print("Nothing found!!")
                    print(line)

        if count!=0:
            ms.commit()
            print("commit")




fn=r'C:\Users\andnn\Desktop\status\status_10468_20190607.log'
gen_sql(fn,10468)

fn=r'C:\Users\andnn\Desktop\status\status_10469_20190607.log'
gen_sql(fn,10469)

# 关闭连接
ms.close()

input("执行完成:")
